package com.xh.web.controller;

import java.io.*;
import java.text.SimpleDateFormat;
import java.util.*;

import cn.hutool.core.io.FileUtil;
import com.xh.web.constant.AjaxResult;
import com.xh.web.constant.Constants;
import com.xh.web.domain.*;
import com.xh.web.domain.vo.ColumnVo;
import com.xh.web.domain.vo.ImportVo;
import com.xh.web.domain.vo.ProjectVo;
import com.xh.web.service.*;
import com.xh.web.util.*;
import com.xh.web.util.velocity.VelocityInitializer;
import com.xh.web.util.velocity.VelocityUtils;
import org.apache.commons.io.IOUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.velocity.Template;
import org.apache.velocity.VelocityContext;
import org.apache.velocity.app.Velocity;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.ClassPathResource;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;


/**
 * 项目Controller
 *
 * @author dongxeufei
 * @date 2020-09-17
 */
@RestController
@RequestMapping("/db/project")
public class DProjectController extends BaseController {
    @Autowired
    private IDProjectService dProjectService;

    @Autowired
    private IDColumnService dColumnService;
    @Autowired
    private IDTableService dTableService;
    @Autowired
    private IDImportService dImportService;
    @Autowired
    private IDTableIndexService dTableIndexService;

    @Autowired
    private ISysConfigService configService;


    /**
     * 查询首页项目
     *
     * @return 项目
     */
    @GetMapping("/select")
    public TableDataInfo selectAllProject(DProject dProject) {
        startPage();
        dProject.setOwnerId(1);
        List<ProjectVo> list = dProjectService.selectAllProject(dProject);
        return getDataTable(list);
    }

    @GetMapping("/list")
    public TableDataInfo list(DProject dProject) {
        startPage();
        dProject.setOwnerId(1);
        List<DProject> list = dProjectService.selectDProjectList(dProject);
        return getDataTable(list);
    }

    /**
     * 导出项目下的表结构
     */
    @GetMapping("/export")
    public AjaxResult export(ColumnVo columnVo, HttpServletResponse response) {
        if(!FileUtil.isDirectory(columnVo.getFilePath())){
            return AjaxResult.error("目录不正确");
        }
        if (columnVo.getExportlayout().equals("excel")) {
            //导出 excel表格
            outPutExcel(columnVo, response);
        } else {
            //导出sql文件
            outPutSql(columnVo, response);
        }
        SysConfig config=new SysConfig();
        config.setConfigKey("export_path");
        config.setConfigValue(columnVo.getFilePath());
        configService.updateConfig(config);
        return AjaxResult.success();

    }

    private void outPutSql(ColumnVo columnVo, HttpServletResponse response) {
        if (StringUtils.isEmpty(columnVo.getTableIds())) {
            String[] tableIds = dTableService.selectTableIdsByProjectId(columnVo.getProjectId());
            columnVo.setTableIds(tableIds);
        }

        // 查询表信息
        String[] tableIds = columnVo.getTableIds();
        DTable dTableTemp = dTableService.selectDTableById(tableIds[0]);
        DProject dProject = dProjectService.selectDProjectById(dTableTemp.getProjectId());

        // 获取模板列表
        List<String> templates = VelocityUtils.getTemplateList("table_" + dProject.getDbType());
        //接收输出的数据
        StringBuilder builder = new StringBuilder();

        VelocityInitializer.initVelocity();
        for (String tableId : tableIds) {
            //获取表
            DTable dTable = dTableService.selectDTableById(tableId);
            VelocityContext context = new VelocityContext();
            context.put("tableCode", dTable.getTableCode());
            context.put("tableName", dTable.getTableName());
            // 获取表的列
            DColumn dColumn = new DColumn();
            dColumn.setTableId(tableId);
            List<DColumn> columns = dColumnService.selectDColumnList(dColumn);
            context.put("columnList", columns);
            //获取表的索引
            DTableIndex dTableIndex = new DTableIndex();
            dTableIndex.setProjectId(dTable.getProjectId());
            dTableIndex.setTableCode(dTable.getTableCode());
            List<DTableIndex> tableIndexs = dTableIndexService.selectDTableIndexList(dTableIndex);
            context.put("indexList", tableIndexs);

            for (String template : templates) {
                // 渲染模板
                StringWriter sw = new StringWriter();
                Template tpl = Velocity.getTemplate(template, Constants.UTF8);
                tpl.merge(context, sw);
                builder.append(sw.toString() + "\n");
            }

        }

        Date date = new Date();
        SimpleDateFormat df = new SimpleDateFormat("yyyyMMdd");//设置日期格式
        String fileName = columnVo.getFilePath()+"/"+dProject.getProjectName() + "_" + df.format(date) + ".sql";

        FileUtil.writeBytes(builder.toString().getBytes(),new File(fileName));


    }

    private void outPutExcel(ColumnVo columnVo, HttpServletResponse response) {
        try {
            //如果tables为空默认导出项目下所有表
            if (StringUtils.isEmpty(columnVo.getTableIds())) {
                String[] tableIds = dTableService.selectTableIdsByProjectId(columnVo.getProjectId());
                columnVo.setTableIds(tableIds);
            }
            //如果tableIds不为空时就导出所选择的表
            String[] tableIds = columnVo.getTableIds();
            List<List<String>> dataTable = new ArrayList<List<String>>();
            DTable dTable = null;
            for (int i = 0; i < tableIds.length; i++) {
                dTable = dTableService.selectDTableById(tableIds[i]);
                if (dTable == null) {
                    continue;
                }
                List rowData = new ArrayList();
                rowData.add(StringUtils.isNotBlank(dTable.getTableCode()) ? dTable.getTableCode() : "");
                rowData.add(StringUtils.isNotBlank(dTable.getTableName()) ? dTable.getTableName() : "");
                //rowData.add("");
                dataTable.add(rowData);
            }
            DProject dProject = dProjectService.selectDProjectById(dTable.getProjectId());

            List<List<String>> dataColumn = null;
            ExportExcelUtil eeu = new ExportExcelUtil();
            HSSFWorkbook workbook = new HSSFWorkbook();

            String[] tableHead = {"表代码", "表中文名"};
            eeu.exportExcelToTable(workbook, 0, "目录", tableHead, dataTable, null,dProject.getProjectName());

            String[] columnHead = {"字段代码", "字段中文名", "数据类型", "长度 ", "非空", "主键"};
            int count = 1;
            for (int i = 0; i < tableIds.length; i++) {
                //正常情况 传过来的表id  我应该都能查询到 为什么会出现查询不到的情况
                DTable dTableColumn = dTableService.selectDTableById(tableIds[i]);
                if (dTableColumn == null) {
                    continue;
                }
                List<DColumn> dColumns = dColumnService.selectColumnByTableId(dTableColumn.getId());
                if (dColumns.isEmpty()) {
                    continue;
                }
                dataColumn = new ArrayList<>();
                for (int j = 0; j < dColumns.size(); j++) {
                    DColumn dColumn = dColumns.get(j);
                    List<String> rowColumnData = new ArrayList();
                    rowColumnData.add(StringUtils.isNotBlank(dColumn.getColumnCode()) ? dColumn.getColumnCode() : "");
                    rowColumnData.add(StringUtils.isNotBlank(dColumn.getColumnName()) ? dColumn.getColumnName() : "");
                    rowColumnData.add(StringUtils.isNotBlank(dColumn.getDataType()) ? dColumn.getDataType() : "");
                    rowColumnData.add(StringUtils.isNotBlank(dColumn.getdLength()) ? dColumn.getdLength() : "");
                    rowColumnData.add(dColumn.getIsEmpty().equals("1") ? "是" : "否");
                    rowColumnData.add(dColumn.getIsPrimarykey().equals("1") ? "是" : "否");
                    //rowColumnData.add(StringUtils.isNotBlank(dColumn.getRemark()) ? dColumn.getRemark() : "");
                    dataColumn.add(rowColumnData);
                }
                eeu.exportExcel(workbook, count++, dTableColumn.getTableCode(), columnHead, dataColumn, null);
            }

            Date date = new Date();
            SimpleDateFormat df = new SimpleDateFormat("yyyyMMdd");//设置日期格式
            String fileName = columnVo.getFilePath()+"/"+dProject.getProjectName()+ ".xls";

            OutputStream tempout = new FileOutputStream(fileName);
            workbook.write(tempout);
            workbook.close();
            tempout.close();
            //FileUtil.writeBytes(workbook.getBytes(),new File(fileName));
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    /**
     * 获取项目详细信息
     */
    @GetMapping(value = "/{id}")
    public AjaxResult getInfo(@PathVariable("id") String id) {
        return AjaxResult.success(dProjectService.selectDProjectById(id));
    }

    /**
     * 新增项目
     */
    @CrossOrigin(origins = "*", maxAge = 3600)
    @PostMapping("/insert")
    public AjaxResult add(@RequestBody DProject dProject) {
        return toAjax(dProjectService.insertDProject(dProject));
    }

    /**
     * 修改项目
     *
     * @return
     */
    @PostMapping("update/{id}")
    public AjaxResult edit(@PathVariable String id, @RequestBody DProject dProject) {
        return toAjax(dProjectService.updateDProject(id, dProject));
    }


    /**
     * 删除单个项目
     */
    @DeleteMapping("delete/{id}")
    public AjaxResult remove1(@PathVariable String id) {
        return toAjax(dProjectService.deleteDProjectById(id));
    }


    /**
     * 查询所有团队成员
     */
    @PostMapping("/selectAuth/{id}")
    public TableDataInfo members(@PathVariable String id) {
        startPage();
        List<DProjectAuth> list = dProjectService.selectMembers(id);
        return getDataTable(list);
    }


    /**
     * 删除团队成员
     */
    @DeleteMapping("/delAuth/{id}")
    public AjaxResult delAuth(@PathVariable String id) {
        return toAjax(dProjectService.deleteAuthById(id));

    }

    /**
     * 导入数据结构
     */
    //  @PreAuthorize("@ss.hasPermi('db:project:import')")
    @PostMapping("/import")
    public AjaxResult importProject(@RequestBody String importJson) {
        //TODO 校验用户
        return dProjectService.importProject(importJson);
    }

    /**
     * 同步数据结构
     */
    @PostMapping("/import/update")
    public AjaxResult importProject1(String id)//String importJson)//List<DTable> tableList, List<DColumn> columnList)
    {
        return dProjectService.updateOrInsertProject(id);
    }


    /**
     * 查询数据库下的所有表
     */
    @PostMapping("/getTables")
    public String getTables() {
        List<Map> list = dImportService.getTables();
        List<DTable> tableList = new ArrayList<>();
        for (int i = 0; i < list.size(); i++) {
            DTable dTable = new DTable();
            dTable.setTableCode((String) list.get(i).get("table_name"));
            dTable.setTableName((String) list.get(i).get("table_comment"));
            tableList.add(dTable);
        }
        List<DColumn> columnList = new ArrayList<>();
        ImportVo importVo = new ImportVo();
        importVo.setTableList(tableList);
        importVo.setColumnList(columnList);
        String importJson = JsonUtil.toJSONString(importVo);
//        HttpClientUtil.doPostJson("http://192.168.8.187:8083/db/import/import",importJson);
        return importJson;
    }


    /**
     * 批量生成代码
     */
    @GetMapping("/download")
    public void batchGenCode(HttpServletResponse response) throws IOException
    {
        //获取客户端地址
        /*List<SysDictData> fileList=dictTypeService.selectDictDataByType("client_url");
        String clientPath=fileList.get(0).getDictValue();
        File file=new File(clientPath);
        byte[] data = FileUtils.File2byte(file);
        genCode(response,data);*/
    }

    /**
     * 生成zip文件
     */
    private void genCode(HttpServletResponse response, byte[] data) throws IOException
    {
        response.reset();
        response.addHeader("Access-Control-Allow-Origin", "*");
        response.addHeader("Access-Control-Expose-Headers", "Content-Disposition");
        response.setHeader("Content-Disposition", "attachment; filename=\"tdclient.zip\"");
        response.addHeader("Content-Length", "" + data.length);
        response.setContentType("application/octet-stream; charset=UTF-8");
        IOUtils.write(data, response.getOutputStream());
    }

    @GetMapping("/template_download")
    public AjaxResult templateDownload(String filePath) {
        String fileName="/dbtemplate.xls";
        try {
            if(!FileUtil.isDirectory(filePath)){
                return AjaxResult.error("目录不正确");
            }
            ClassPathResource classPathResource = new ClassPathResource("file/dbtemplate.xls");
            FileUtil.writeFromStream(classPathResource.getInputStream(),new File(filePath+fileName));
            SysConfig config=new SysConfig();
            config.setConfigKey("export_path");
            config.setConfigValue(filePath);
            configService.updateConfig(config);
            return AjaxResult.success("下载成功");
        } catch (IOException e) {
            return AjaxResult.error("下载失败");
        }

    }

    @PostMapping("/excel_inport")
    public AjaxResult excelInport(MultipartFile file,String dbCode) {
        if(file==null){
            return AjaxResult.error("文件不能为空");
        }
        //取出文件扩展名
        String originalFilename = file.getOriginalFilename();
        String fileType = originalFilename.substring(originalFilename.lastIndexOf(".") + 1).toLowerCase();
        try {
            if(file.isEmpty()){
                return AjaxResult.error("文件不能为空");
            }else if(!fileType.equals("xls")&&!fileType.equals("xlsx")){
                return AjaxResult.error("只能上传excel文件");
            }else if(StringUtils.isNull(dbCode)){
                return AjaxResult.error("数据库编码不能为空");
            }
            //验证模板
            if(checkTemplate(file)){
                return AjaxResult.error("模板不正确，请按模板填写");
            }

            DImport dImport=new DImport();
            if(StringUtils.isNotNull(dbCode)){
                DProject project = new DProject();
                project.setProjectCode(dbCode);
                project.setOwnerId(1);
                List<ProjectVo> dProjects = dProjectService.selectAllProject(project);
                if (dProjects.isEmpty()) {
                    return AjaxResult.error("数据库未查询到,请检查数据库编码是否正确");
                }
                ProjectVo projectVo=dProjects.get(0);
                dImport.setProjectName(projectVo.getProjectName());
                dImport.setProjectId(projectVo.getId());
            }

            //String path= RedisUtil.getConfigValue("db_upload_path");
            //String path=iSysConfigService.selectConfigByKey("db_upload_path");
            //String path = "E:\\workspace\\tool\\tmpfile\\";
            //UUID uuid = UUID.randomUUID();
            //String newFileName = uuid+"."+fileType;
            //存储路径拼接Uuid文件名  最终存储文件路径
            //String filePath=path+newFileName;
            //file.transferTo(new File(filePath));

            //插入导入记录
            dImport.setProjectCode(dbCode);
            dImport.setCreateBy("admin");
            dImport.setCreateDate(new Date());
            dImport.setUpdateDate(new Date());
            dImport.setUpdateBy("admin");
            dImport.setDelFlag("0");
            dImport.setInportStatus("0");
            //dImport.setPath(filePath);
            //dImport.setFile(newFileName);
            dImport.setImportMode("excel导入");
            dImport.setCreateName("admin");
            dImportService.insertDImport(dImport);

            syncImport(dImport.getId(),file);//导入

            return AjaxResult.success();
        } catch (Exception e) {

            return AjaxResult.error("导入失败");
        }

    }

    private boolean checkTemplate(MultipartFile file) throws IOException, InvalidFormatException {
        boolean isOk=false;
        Workbook workbook= WorkbookFactory.create(file.getInputStream());
        Sheet sheet=workbook.getSheetAt(0);
        Row row=sheet.getRow(0);
        Cell cell=row.getCell(0);
        Cell cell1=row.getCell(1);
        if(cell==null||!ExcelUtil.getCellValue(cell).equals("表代码[必填]")
        ||cell1==null||!ExcelUtil.getCellValue(cell1).equals("表中文名")){
            return true;
        }
        Sheet sheet1=workbook.getSheetAt(1);
        Row row1=sheet1.getRow(1);
        Cell columnCell0=row1.getCell(0);
        Cell columnCell1=row1.getCell(1);
        if(columnCell0==null||!ExcelUtil.getCellValue(columnCell0).equals("字段代码[必填]")
                ||columnCell1==null||!ExcelUtil.getCellValue(columnCell1).equals("字段中文名[必填]")){
            return true;
        }
        return  isOk;
    }

    //异步导入
    private void syncImport(String importId,MultipartFile file){
        dProjectService.syncInportDb(importId,file);
    }

    @GetMapping("/saveas")
    public AjaxResult saveas(String saveAsPath) {
        int a=saveAsPath.lastIndexOf("\\")+1;
        int b=saveAsPath.length();
        //String fileName=saveAsPath.substring(a,b);
        String content=saveAsPath.substring(0,a);

        try {
            if(!FileUtil.isDirectory(content)){
                return AjaxResult.error("目录不正确");
            }
            ClassPathResource classPathResource = new ClassPathResource("dbs/tdmodel.db");
            FileUtil.writeFromStream(classPathResource.getInputStream(),new File(saveAsPath));
            //File file=new File("./tdmodel.db");
            //FileUtil.copy("./tdmodel.db",saveAsPath,true);
            return AjaxResult.success("保存成功");
        } catch (Exception e) {
            e.printStackTrace();
            return AjaxResult.error("保存失败");
        }

    }

    @PostMapping("/openfile")
    public AjaxResult openfile(MultipartFile file) {
        if(file==null){
            return AjaxResult.error("文件不能为空");
        }
        ClassPathResource classPathResource = new ClassPathResource("dbs/tdmodel.db");
        //取出文件扩展名
        String originalFilename = file.getOriginalFilename();
        String fileType = originalFilename.substring(originalFilename.lastIndexOf(".") + 1).toLowerCase();

        try {
            if(file.isEmpty()){
                return AjaxResult.error("文件不能为空");
            }else if(!fileType.equals("db")){
                return AjaxResult.error("选择的不是TD导出的文件");
            }
            file.transferTo(classPathResource.getFile());
            //file.transferTo(new File("E:\\workspace\\tool\\picture\\tdmodel2.db"));
            return AjaxResult.success("保存成功");
        } catch (Exception e) {
            e.printStackTrace();
            return AjaxResult.error("保存失败");
        }

    }



}
